<?php
require_once(Kohana::find_file('vendor','phprpc/phprpc_client'));
/**
 * Created by PhpStorm.
 * User: fengtian
 * Date: 15-3-19
 * Time: 上午8:33
 */
class Controller_Checkstock extends Controller_Admin
{
    public function before()
    {
        try {
            ACL::Required('checkstock');
        } catch ( HTTP_Exception_403 $e ) {
            Message::error('sorry，你没有权限进这个目录');
            $this->request->redirect ( URL::base (true) );
        }

        parent::before();
    }
    public function action_index()
    {
        $this->title = '<h4>库存紧急盘点预警</h4>';
        $view        = View::factory('index');

        $get = $_GET;

        if(empty($_GET)){
            $get['status'] = 4;
        }

        $get['start_time'] = isset($_GET['start_time'])?$_GET['start_time']:date("Y-m-d",time());

        $re = DB::select(DB::expr('upper(i.sku) as sku'))
            ->from(array(
                'inventories',
                'i'
            ))
            ->join(array(
                'ldt_skus',
                'l'
            ))
            ->on('i.sku','=','l.sku');
            //->where('l.onsale','=',1);功能转移

        $get['status']?$re->where('l.status','=',$get['status']):'';

        if(!empty($get['storage1'])&&!empty($get['storage2'])){
            $re->where('i.storage_position','>=',trim($get['storage1'])*10000);
            $re->where('i.storage_position','<=',trim($get['storage2'])*10000);
        }

        if(!empty($get['storage1'])&&empty($get['storage2'])){
            $re->where('i.storage_position','like','%'.trim($get['storage1'])).'%';
        }

        if(empty($get['storage1'])&&!empty($get['storage2'])){
            $re->where('i.storage_position','like','%'.trim($get['storage2'])).'%';
        }

        if(!empty($get['storage'])){
            $re->where('i.storage_position','like',trim($get['storage']).'%');
        }

        if(isset($get['sku'])&&!empty($get['sku'])){
            $re->where('i.sku','=',trim($get['sku']));
        }

        if(isset($skus)&&$skus){
            $re->where('i.sku','in',$skus);
        }

        $re->where('i.inventory_date','=',date('Y-m-01',strtotime($get['start_time'])));

        $re_count = clone $re;

        $page = self::page($re_count,isset($_GET['limit'])?$_GET['limit']:10);
        $re->limit($page->items_per_page)
            ->offset($page->_offset);

        $lists = $re->execute('newerp')
            ->as_array(NULL,'sku');

        //获取出入库数据
        $end   = date('Y-m-d',strtotime("+1 day",strtotime($get['start_time'])));
        $begin = date('Y-m-01 00:00:00',strtotime($get['start_time']));

        empty($lists)?$lists = array():$lists = checkstock::install_lists($begin,$end,$lists,true);

        $view->set('nums',checkstock::getRecord());

        $view->set('first',$page->current_first_item)
            ->set('lists',$lists)
            ->set('page',$page)
            ->set('get',$get);
        $view->set('record',DB::select(DB::expr('COUNT(id)AS number'),'add_time')
            ->from("ldt_skus")
            ->execute('newerp')
            ->current());
        $view->set('status',checkstock::getStatusdicsValue('ldt_skus','status',NULL,0));

        $this->response->body($view);
    }

    public function action_scan()
    {
        $erp_orders_id = substr($_GET['erp_orders_id'],1);
        //var_dump($id);

        $sku = DB::select('orders_sku')
            ->from('erp_orders_products')
            ->where('erp_orders_id','=',$erp_orders_id)
            ->execute('readonlyerp')
            ->current();
        //var_dump($sku);
        if($sku){
            $view = View::factory('sku')
                ->set('sku',$sku['orders_sku'])
                ->render();
            $json = array(
                'status' => 'Y',
                'view'   => $view
            );
        }else{
            $json = array(
                'status' => 'N',
                'info'   => '该订单号有误，未查到对应的sku'
            );
        }
        echo json_encode($json);
        exit;
    }

    public function action_warning()
    {
        $ok = 0;
        if($this->request->is_post()&&$_POST){
            $st = DB::select('id','status')
                ->from('ldt_skus')
                ->where('sku','=',$_POST['sku'])
                ->execute('newerp')
                ->current();

            if($st){
                if($st['status']==1){
                    $ok = DB::update('ldt_skus')
                        ->set(array('status' => 3))
                        ->where('id','=',$st['id'])
                        ->execute('newerp');
                }else{
                    $have = DB::select('id')
                        ->from('pandians')
                        ->where('pd_time','=',date("Y-m-d",time()))
                        ->execute('newerp')
                        ->get('id',0);
                    if($have){
                    }else{
                        $ok = DB::update('ldt_skus')
                            ->set(array('status' => 3))
                            ->where('id','=',$st['id'])
                            ->execute('newerp');
                    }
                }
            }
            $ok?Message::success("紧急盘点预警成功"):Message::warn("今天已紧急盘点!");
            exit;
        }
    }

    //常规紧急库存盘点
    public function action_spare()
    {
        $this->title = __("常规紧急库存盘点");
        $view        = View::factory("spare");

        if(empty($_GET)||(isset($_GET['page'])&&count($_GET)==1)){
            $_GET['status'] = $_GET['order'] = $_GET['sort'] = 1;
        }
        isset($_GET['down'])?$download = TRUE:$download = FALSE;
        $_GET['start_time'] = isset($_GET['start_time'])?$_GET['start_time']:date("Y-m-d",time());

        $re = DB::select(DB::expr('upper(i.sku) as sku'))
            ->from(array(
                'inventories',
                'i'
            ))
            ->join(array(
                'ldt_skus',
                'l'
            ))
            ->on('i.sku','=','l.sku');
        isset($_GET['source'])?$re->where('source', '=', $_GET['source']):$re->where('source', '=', 1);

        $re = checkstock::condition($re,$_GET);

        isset($_GET['limit'])?$re->where('l.status','=',1):'';
        //isset($_GET['limit'])&&$re->group_by('i.storage_position');
        $re_count = $re_clone = clone $re;

        if(!$download){
            $page = self::page($re_count,isset($_GET['limit'])?$_GET['limit']:10);

            $re->offset($page->_offset)
                ->limit($page->items_per_page)
                ->order_by('l.sale','DESC');
        }

        $lists = $re->execute('newerp')
            ->as_array(NULL,'sku');

        //盘点完前5000个后盘点后5000个
        if(empty($lists) && !$download && isset($_GET['limit'])) {
            $re_clone->offset(5000)
                ->limit($page->items_per_page)
                ->order_by('l.sale','DESC');

            $lists = $re_clone->execute('newerp')
                ->as_array(NULL,'sku');
        }

        //获取出入库数据
        $end   = date('Y-m-d',strtotime("+1 day",strtotime($_GET['start_time'])));
        $begin = date('Y-m-01 00:00:00',strtotime($_GET['start_time']));

        isset($_GET['limit'])||$download?$storages = Skusystemapi::getSkuPlaces($lists):TRUE;

        empty($lists)?$lists = array():$lists = checkstock::install_lists($begin,$end,$lists);

        //跳转到下载页面
        isset($_GET['limit'])||$download?$this->download($lists,$storages):TRUE;

        $download?TRUE:$view->set('nums',checkstock::getRecord());

        $view->set('first',$page->current_first_item)
            ->set('lists',$lists)
            ->set('page',$page)
            ->set('get',$_GET);
        $view->set('record',DB::select(DB::expr('COUNT(id)AS number'),'add_time')
            ->from("ldt_skus")
            ->execute('newerp')
            ->current());
        $view->set('status',checkstock::getStatusdicsValue('ldt_skus','status',NULL,0));

        $this->response->body($view);
    }


    //分页
    public function page($re_count,$limit)
    {
        $count = $re_count->select(DB::expr('COUNT(i.id)AS totle'))
            ->execute('newerp')
            ->get('totle',0);
        return Pagination::factory(array(
            'total_items'    => $count,
            'items_per_page' => $limit,
            'uri'            => $this->request->uri()
        ));
    }

    //单个盘点
    public function action_updateSingle()
    {
        $respons = '';
        if(isset($_GET['in_id'])&&$_GET['sku']){
            $view = View::factory('single');

            $re = DB::select_array(array(
                'sku',
                'sku_name'
            ))
                ->from('inventories')
                ->where('id','=',$_GET['in_id'])
                ->where('sku','=',$_GET['sku'])
                ->execute('newerp')
                ->current();

            $view->set('sku_info',$re)
                ->set('num',$_GET['num']);
            if(isset($_GET['stock_old'])){
                $view->set('stock_old',$_GET['stock_old']);
            }
            if(isset($_GET['stock_new'])){
                $view->set('stock_new',$_GET['stock_new']);
            }
            $respons = array("view" => $view->render());
        }

        echo json_encode($respons);
        exit;
    }

    public function action_checkSingle()
    {
        if(isset($_POST)){
            $user_id   = User::active_user()->id;
            $sku       = $_POST['sku'];
            $skus      = array('sku' => $sku);
            $time      = date('Y-m-d H:i:s');
            $quantity  = isset($_POST['stock_new'])?$_POST['stock_new']:0; //盘点量
            $quantity2 = isset($_POST['stock_old'])?$_POST['stock_old']:0; //库存量
            $sec       = time();
            $end       = date('Y-m-d',strtotime("+1 day",$sec));
            $begin     = date('Y-m-01 00:00:00',$sec);
            $lists     = Checkstock::install_lists($begin,$end,$skus);
            $quantity3 = $lists[$sku]['quantity']; //盘点记录后那段时间更新后的库存量
            $num       = $quantity-$quantity2; //实际盘点量-下载表格时的库存量
            //$status 3:盘点数量相等，1：盘亏，2：盘赢
            $status = 3;
            if($num>0){
                $status = 2;
            }elseif($num<0){
                $status = 1;
            }

            $storages = Skusystemapi::getSkuPlaces($skus);
            $storages = array_combine(Arr::pluck($storages,'sku'),$storages);

            $info                     = array();
            $info['sku_name']         = $lists[$sku]['sku_name'];
            $info['sku_standard']     = '';
            $info['storage_position'] = $storages[$sku]['place'];

            $condition1 = array(
                'sku'           => $sku,
                'quantity'      => $quantity,
                'quantity2'     => $quantity2,
                'quantity3'     => $quantity3,
                'pd_time'       => date('Y-m-d',time()),
                'op_time'       => $time,
                'download_time' => $time,
                'upload_time'   => $time,
                'user_id'       => $user_id,
                'num'           => $num,
                'status'        => $status
            );

            $insert_id = self::insert_pandian(array_merge($condition1,$info));
            if($insert_id){
                $condition = array(
                    'sku'           => $sku,
                    'delivery_date' => $time,
                    'quantity'      => abs($num),
                    'order_id'      => 0
                );
                $condition = array_merge($condition,$info);
                //获取系统默认单价
                $supplier = new Service_Common();
                $result   = json_encode($supplier->get_sku_price($sku));
                if($num>0){
                    //盘赢自动入库
                    $condition['single_price'] = $result;
                    $condition['remark']       = '盘赢自动入库，盘点单id：'.$insert_id;
                    $condition['type']         = 30;
                    Warehouse::insert_warehouse($condition);
                }else{
                    if($num<0){
                        //盘亏自动出库
                        $condition['single_price']       = 0;
                        $condition['remark']             = '盘亏自动出库，盘点单id：'.$insert_id;
                        $condition['type']               = 20;
                        $condition['orders_products_id'] = 0;
                        Delivery::insert_delivery1($condition);
                    }
                }
                DB::update('ldt_skus')
                    ->set(array('status' => $_POST['status']))
                    ->where('sku','=',$sku)
                    ->execute('newerp');
                $json_array = array(
                    'info'   => 'success',
                    'status' => 'Y'
                );
            }else{
                $json_array = array(
                    'info'   => 'already pandian it!',
                    'status' => 'N'
                );
            }
        }else{
            $json_array = array(
                'info'   => 'pandian fail',
                'status' => 'N'
            );
        }
        Message::success($json_array['info']);
        $this->request->redirect("checkstock/spare?sku=".$sku);
    }

    public function check_pandian($sku,$date)
    {
        return DB::select('id')
            ->from('pandians')
            ->where('sku','=',$sku)
            ->where('pd_time','=',$date)
            ->execute('newerp')
            ->get('id',0);
    }

    public static function insert_pandian($condition)
    {
        list($insert_id,) = DB::insert('pandians',array(
            'sku',
            'sku_name',
            'storage_position',
            'sku_standard',
            'quantity',
            'quantity2',
            'quantity3',
            'pd_time',
            'op_time',
            'download_time',
            'upload_time',
            'user_id',
            'status',
            'num'
        ))
            ->values(array(
                $condition['sku'],
                $condition['sku_name'],
                $condition['storage_position'],
                $condition['sku_standard'],
                $condition['quantity'],
                $condition['quantity2'],
                $condition['quantity3'],
                $condition['pd_time'],
                $condition['op_time'],
                $condition['download_time'],
                $condition['upload_time'],
                $condition['user_id'],
                $condition['status'],
                $condition['num']
            ))
            ->execute('newerp');
        return $insert_id;
    }

    //下载(总是下载当前没有盘点的前1000个)
    public function download($lists,$storages)
    {
        set_time_limit(0);
        ini_set('memory_limit','2048M');

        require_once(Kohana::find_file('vendor','PHPExcel/PHPExcel/IOFactory'));
        $this->objReader = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel     = new PHPExcel();
        $objPHPExcel->getProperties()
            ->setCreator("YKS")
            ->setLastModifiedBy("YKS")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet = $objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1','SKU');
        $objActSheet->setCellValue('B1','单据下载时间');
        $objActSheet->setCellValue('C1','品名');
        $objActSheet->setCellValue('D1','储位');
        $objActSheet->setCellValue('E1','库存量');
        $objActSheet->setCellValue('F1','盘点时间（xxxx-xx-xx）');
        $objActSheet->setCellValue('G1','实际盘点量');
        $objActSheet->setCellValue('H1','借料备注量');

        $storages = array_combine(Arr::pluck($storages,'sku'),$storages);
        /*foreach( $storages as $st) {
            unset($st['sku']);
            $st = $st['place'];
        }

        $storages = asort($storages);*/
        $time     = date('Y-m-d H:i:s');
        $i        = 0;

        foreach($lists as $list){
            $key = $i+2;
            if(isset($_GET['limit']) && $key > $_GET['limit'] + 2)break;
            $objActSheet->setCellValue('A'.$key,$list['sku']);
            $objActSheet->setCellValue('B'.$key,$time);
            $objActSheet->setCellValue('C'.$key,$list['sku_name']);
            $objActSheet->setCellValue('D'.$key,isset($storages[$list['sku']]['place'])?$storages[$list['sku']]['place']:'');
            $objActSheet->setCellValue('E'.$key,$list['quantity']);
            $objActSheet->setCellValue('F'.$key,date('Y-m-d'));
            $objActSheet->setCellValue('H'.$key,$list['lend']);

            $i++;
        }

        $objPHPExcel->getActiveSheet()
            ->setTitle('常规紧急库存盘点报表');
        $objPHPExcel->setActiveSheetIndex(0);
        $day      = date("m-d");
        $filename = $day."常规紧急库存盘点报表.xls";
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename='.$filename);
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    //库存盘点重构
    public function action_upload()
    {
        set_time_limit(0);
        $this->title = '盘点单';
        $time        = date('Y-m-d H:i:s');
        if(isset($_GET['page'])&&$_GET['page']>1){
            $sql  = "select op_time from pandians order by op_time desc limit 1";
            $date = DB::query(Database::SELECT,$sql)
                ->execute('newerp')
                ->current();
            if(isset($date['op_time'])){
                $time = $date['op_time'];
            }
        }

        ini_set('memory_limit','500M');
        require_once(Kohana::find_file('vendor','PHPExcel/PHPExcel/IOFactory'));
        $xls = $_FILES['xls']['tmp_name'];
        if($xls){
            $objPHPExcel = PHPExcel_IOFactory::load($xls);
            $data        = $objPHPExcel->getActiveSheet()
                ->toArray(NULL,TRUE,TRUE,TRUE);
            $data = self::checkxls($data);
            if($data['error']!='')
            {
                $str = $data['error'];
                $data = self::checkxls($data);
            }
            unset($data['error']);
            if(count($data)==1)
            {
                $str = str_replace("<br/>",'',$str);
                header("Content-type: text/html; charset=utf-8");
                $alert_msg = "alert('{$str}');";
                $gourl = 'history.go(-1);';
                echo "<script>{$alert_msg}{$gourl}</script>";
                exit;
                $this->request->redirect("checkstock/spare");
            }
            $lines = checkstock::upload($data,$time);
        }
        $tablename = 'pandians';
        $result    = $this->fenye2($tablename,$condition = array(
            'time'           => $time,
            'items_per_page' => 10
        ));
        $view      = View::factory('up_result')
            ->set('datas',$result['datas'])
            ->set('pager_links',$result['pager_links'])
            ->set('lines',isset($lines['num'])?$lines['num']:array())
            ->set('final',isset($lines['error'])?$lines['error']:'')
            ->set('error',$str);
        $this->response->body($view);
    }

    public function sortxls($data)
    {
        $new = array();
        if(!empty($data))
        {
            foreach($data as $d)
            {
                $new[] = $d;
            }
        }

        return $new;
    }

    private function fenye2($tablename,$condition)
    {
        $items_per_page = $condition['items_per_page'];
        $pre            = Pandianinout::getListInfoCondition(Pandianinout::getTotlePre($tablename),$condition,FALSE,$tablename);
        $pagination     = Pagination::factory(array(
            'total_items'    => Pandian::getTotle($pre),
            'view'           => 'pagination/ajax_basic',
            'items_per_page' => $items_per_page,
            'uri'            => $this->request->uri(),
        ));
        if(isset($_GET['page'])&&!empty($_GET['page'])){
            $offset = ($_GET['page']-1)*$items_per_page;
        }else{
            $offset = 0;
        }
        $pre         = Pandianinout::getListInfoCondition(Pandianinout::getListPre($tablename),$condition,TRUE,$tablename);
        $datas       = Pandianinout::getList($items_per_page,$offset,$pre);
        $pager_links = $pagination->render();
        $result      = array(
            'datas'       => $datas,
            'pager_links' => $pager_links
        );
        return $result;
    }

    //已盘点、紧急已盘点数据更新
    public function action_updatePandian()
    {
        $respons = '';
        if(isset($_GET['in_id'])&&$_GET['sku']){
            $view = View::factory('updatePandian');

            $re = DB::select_array(array(
                'sku',
                'sku_name'
            ))
                ->from('inventories')
                ->where('id','=',$_GET['in_id'])
                ->where('sku','=',$_GET['sku'])
                ->execute('newerp')
                ->current();

            $view->set('sku_info',$re)
                ->set('num',$_GET['num']);
            if(isset($_GET['stock_old'])){
                $view->set('stock_old',$_GET['stock_old']);
            }
            if(isset($_GET['stock_new'])){
                $view->set('stock_new',$_GET['stock_new']);
            }
            $respons = array("view" => $view->render());
        }

        echo json_encode($respons);
        exit;
    }

    public function action_doUpdatePandian()
    {
        if(isset($_POST)){
            $today   = date('Y-m-d');
            $sku     = $_POST['sku'];
            $res     = DB::select()
                ->from('pandians')
                ->where('sku','=',$sku)
                ->where('pd_time','=',$today)
                ->execute('newerp')
                ->current();
            $user_id = User::active_user()->id;

            $skus      = array('sku' => $sku);
            $time      = date('Y-m-d H:i:s');
            $quantity  = isset($_POST['stock_new'])?$_POST['stock_new']:0; //盘点量
            $quantity2 = isset($_POST['stock_old'])?$_POST['stock_old']:0; //库存量
            $sec       = time();
            $end       = date('Y-m-d',strtotime("+1 day",$sec));
            $begin     = date('Y-m-01 00:00:00',$sec);
            $lists     = Checkstock::install_lists($begin,$end,$skus);
            $quantity3 = $lists[$sku]['quantity']; //盘点记录后那段时间更新后的库存量
            $num       = $quantity-$quantity2; //实际盘点量-下载表格时的库存量
            //$status 3:盘点数量相等，1：盘亏，2：盘赢
            $status = 3;
            if($num>0){
                $status = 2;
            }elseif($num<0){
                $status = 1;
            }

            $storages = Skusystemapi::getSkuPlaces($skus);
            $storages = array_combine(Arr::pluck($storages,'sku'),$storages);

            $info                     = array();
            $info['sku_name']         = $lists[$sku]['sku_name'];
            $info['sku_standard']     = '';
            $info['storage_position'] = $storages[$sku]['place'];

            $condition1 = array(
                'sku'           => $sku,
                'quantity'      => $quantity,
                'quantity2'     => $quantity2,
                'quantity3'     => $quantity3,
                'pd_time'       => $today,
                'op_time'       => $time,
                'download_time' => $time,
                'upload_time'   => $time,
                'user_id'       => $user_id,
                'num'           => $num,
                'status'        => $status
            );

            if($res){
                //今日已盘点
                $insert_id = self::update_pandian(array_merge($condition1,$info));
            }else{
                //今日未盘点
                $insert_id = self::insert_pandian(array_merge($condition1,$info));
            }
            if($insert_id){
                $condition = array(
                    'sku'           => $sku,
                    'delivery_date' => $time,
                    'quantity'      => abs($num),
                    'order_id'      => 0
                );
                $condition = array_merge($condition,$info);
                //获取系统默认单价
                $supplier = new Service_Common();
                $result   = json_encode($supplier->get_sku_price($sku));
                if($num>0){
                    //盘赢自动入库
                    $condition['single_price'] = $result;
                    $condition['remark']       = '盘赢自动入库，盘点单id：'.$insert_id;
                    $condition['type']         = 30;
                    Warehouse::insert_warehouse($condition);
                }else{
                    if($num<0){
                        //盘亏自动出库
                        $condition['single_price']       = 0;
                        $condition['remark']             = '盘亏自动出库，盘点单id：'.$insert_id;
                        $condition['type']               = 20;
                        $condition['orders_products_id'] = 0;
                        Delivery::insert_delivery1($condition);
                    }
                }
                DB::update('ldt_skus')
                    ->set(array('status' => $_POST['status']))
                    ->where('sku','=',$sku)
                    ->execute('newerp');
                $json_array = array(
                    'info'   => 'success',
                    'status' => 'Y'
                );
            }else{
                $json_array = array(
                    'info'   => 'already pandian it!',
                    'status' => 'N'
                );
            }
        }else{
            $json_array = array(
                'info'   => 'pandian fail',
                'status' => 'N'
            );
        }
        Message::success($json_array['info']);
        $this->request->redirect("checkstock/spare?sku=".$sku);
    }

    public static function update_pandian($condition)
    {
        $update_id = DB::update('pandians')
            ->set(array(
                'sku_name'         => $condition['sku_name'],
                'storage_position' => $condition['storage_position'],
                'sku_standard'     => $condition['sku_standard'],
                'quantity'         => $condition['quantity'],
                'quantity2'        => $condition['quantity2'],
                'quantity3'        => $condition['quantity3'],
                'op_time'          => $condition['op_time'],
                'download_time'    => $condition['download_time'],
                'upload_time'      => $condition['upload_time'],
                'user_id'          => $condition['user_id'],
                'status'           => $condition['status'],
                'num'              => $condition['num']
            ))
            ->where('sku','=',$condition['sku'])
            ->where('pd_time','=',$condition['pd_time'])
            ->execute('newerp');
        return $update_id;
    }

    public function action_active()
    {
        $this->title = '活动记录';
        $view        = View::factory('active');
        if(isset($_GET)){
            $sql = DB::select()
                ->from('active');
            if(isset($_GET['stage'])&&($_GET['stage'])!="全部"){
                $sql->where('stage','=',$_GET['stage']);
                $view->set('stage',$_GET['stage']);
            }
            if(isset($_GET['status'])&&is_numeric($_GET['status'])){
                if($_GET['status']==2){
                    $_GET['status'] = 0;
                }
                $sql->where('status','=',$_GET['status']);
                $view->set('status',$_GET['status']);
            }
            if(isset($_GET['start_time'])&&isset($_GET['end_time'])){
                $view->set('begin',$_GET['start_time'])
                    ->set('end',$_GET['end_time']);

                if($_GET['start_time']&&$_GET['end_time']){
                    if($_GET['start_time']>=$_GET['end_time']){
                        Message::error('开始时间不能大于等于结束时间');
                        $this->response->body($view);
                        return;
                    }
                    $sql->where('begin','>=',$_GET['start_time'])
                        ->where('end','<=',$_GET['end_time']);
                }elseif($_GET['start_time']&&empty($_GET['end_time'])){
                    $sql->where('begin','>=',$_GET['start_time']);
                }elseif($_GET['end_time']&&empty($_GET['start_time'])){
                    $sql->where('end','<=',$_GET['end_time']);
                }
            }
            $res        = $sql->execute('newerp')
                ->as_array();
            $pagination = Pagination::factory(array(
                'total_items'    => count($res),
                'items_per_page' => 20,
                'uri'            => $this->request->uri(),
            ));
            $res        = array_slice($res,$pagination->current_first_item-1,$pagination->items_per_page);
            if($res){
                $view->set('result',$res)
                    ->set('pagination',$pagination);
            }else{
                Message::error('没有查询到结果');
            }
        }
        $this->response->body($view);
    }

    public function action_close_active()
    {
        $id  = $_GET['id'];
        $res = DB::update('active')
            ->set(array('status' => '0'))
            ->where('id','=',$id)
            ->execute('newerp');
        if($res){
            $json = array('info' => '该活动关闭成功');
        }else{
            $json = array('info' => '该活动关闭失败');
        }
        echo json_encode($json);
        exit;
    }

    public function action_open_active()
    {
        $id  = $_GET['id'];
        $res = DB::update('active')
            ->set(array('status' => '1'))
            ->where('id','=',$id)
            ->execute('newerp');
        if($res){
            $json = array('info' => '该活动开启成功');
        }else{
            $json = array('info' => '该活动开启失败');
        }
        echo json_encode($json);
        exit;
    }

    public function action_update_active()
    {
        $id   = $_GET['id'];
        $view = View::factory('updateActive');
        $res  = DB::select()
            ->from('active')
            ->where('id','=',$id)
            ->execute('newerp')
            ->current();
        $str  = $view->set('result',$res)
            ->render();
        if($res){
            $json = array('view' => $str);
            echo json_encode($json);
            exit;
        }
    }

    public function action_update_active_detail()
    {
        if(isset($_POST)){
            if($_POST['begin']>=$_POST['end']){
                Message::error('修改失败，开始时间不能大于等于结束时间');
                return;
            }
            $data = array(
                'stage'    => $_POST['stage'],
                'begin'    => $_POST['begin'],
                'end'      => $_POST['end'],
                'add_time' => date('Y-m-d H:i:s')
            );

            $res = DB::update('active')
                ->set($data)
                ->where('id','=',$_POST['number'])
                ->execute('newerp');
            if($res){
                $json = array('info' => '修改成功');
                Message::success($json['info']);
            }else{
                $json = array('info' => '修改失败');
                Message::error($json['info']);
            }
        }
    }

    public function action_add_active()
    {
        $view = View::factory('addActive');
        $str  = $view->render();
        $json = array('view' => $str);
        echo json_encode($json);
        exit;
    }

    public function action_add_active_detail()
    {
        if(isset($_POST)){
            if($_POST['begin']>=$_POST['end']){
                Message::error('添加失败，开始时间不能大于等于结束时间');
                return;
            }
            $data   = array(
                'stage'    => $_POST['stage'],
                'begin'    => $_POST['begin'],
                'end'      => $_POST['end'],
                'status'   => 1,
                'add_time' => date('Y-m-d H:i:s')
            );
            $result = DB::select()
                ->from('active')
                ->where('stage','=',$_POST['stage'])
                ->where('begin','=',$_POST['begin'])
                ->where('end','=',$_POST['end'])
                ->execute('newerp')
                ->current();
            if($result){
                $json = array('info' => '该活动已有，添加失败');
                Message::error($json['info']);
            }else{
                $res = DB::insert('active',array_keys($data))
                    ->values(array_values($data))
                    ->execute('newerp');
                if($res){
                    $json = array('info' => '添加成功，该活动已开启');
                    Message::success($json['info']);
                }else{
                    $json = array('info' => '该活动已有，添加失败');
                    Message::error($json['info']);
                }
            }
        }
    }

    public function action_skuactive()
    {
        $this->title = '活动SKU记录';
        $view        = View::factory('skuactive');
        if(isset($_GET)){
            $sql = DB::select()
                ->from('active')
                ->join('active_skus')
                ->on('active.id','=','active_skus.active_id');
            if(isset($_GET['stage'])&&($_GET['stage'])!="全部"){
                $sql->where('active.stage','=',$_GET['stage']);
                $view->set('stage',$_GET['stage']);
            }
            if(isset($_GET['status'])&&is_numeric($_GET['status'])){
                if($_GET['status']==2){
                    $_GET['status'] = 0;
                }
                $sql->where('active.status','=',$_GET['status']);
                $view->set('status',$_GET['status']);
            }
            if(isset($_GET['sku'])&&$_GET['sku']){
                $sql->where('active_skus.sku','=',$_GET['sku']);
                $view->set('sku',$_GET['sku']);
            }
            if(isset($_GET['start_time'])&&isset($_GET['end_time'])){
                $view->set('begin',$_GET['start_time'])
                    ->set('end',$_GET['end_time']);

                if($_GET['start_time']&&$_GET['end_time']){
                    if($_GET['start_time']>=$_GET['end_time']){
                        Message::error('开始时间不能大于等于结束时间');
                        $this->response->body($view);
                        return;
                    }
                    $sql->where('active.begin','>=',$_GET['start_time'])
                        ->where('active.end','<=',$_GET['end_time']);
                }elseif($_GET['start_time']&&empty($_GET['end_time'])){
                    $sql->where('active.begin','>=',$_GET['start_time']);
                }elseif($_GET['end_time']&&empty($_GET['start_time'])){
                    $sql->where('active.end','<=',$_GET['end_time']);
                }
            }
            $res        = $sql->execute('newerp')
                ->as_array();
            $pagination = Pagination::factory(array(
                'total_items'    => count($res),
                'items_per_page' => 20,
                'uri'            => $this->request->uri(),
            ));
            $res        = array_slice($res,$pagination->current_first_item-1,$pagination->items_per_page);
            if($res){
                $view->set('result',$res)
                    ->set('pagination',$pagination);
            }else{
                Message::error('没有查询到结果');
            }
        }
        $this->response->body($view);
    }

    public function action_add_sku()
    {
        $view = View::factory('addSku');
        $str  = $view->set('id',$_GET['id'])
            ->render();
        $json = array('view' => $str);
        echo json_encode($json);
        exit;
    }

    public function action_add_sku_detail()
    {
        if(isset($_POST)){
            $sku = strtoupper($_POST['sku']);
            $id  = $_POST['id'];

            $res = DB::select()
                ->from('active_skus')
                ->where('active_id','=',$id)
                ->where('sku','=',$sku)
                ->execute('newerp')
                ->current();
            if($res){
                Message::error('该sku已添加至该活动中，添加失败');
            }else{
                $result = DB::insert('active_skus',array(
                    'sku',
                    'active_id',
                    'add_time'
                ))
                    ->values(array(
                        $sku,
                        $id,
                        date('Y-m-d H:i:s')
                    ))
                    ->execute('newerp');

                if($result){
                    Message::success('添加成功');
                }else{
                    Message::error('添加失败');
                }
            }
        }
    }

    public function action_upload_sku()
    {
        $this->title = '批量上传SKU';
        $view        = View::factory('uploadsku')
            ->set('id',$_GET['id']);
        $this->response->body($view);
    }

    public function action_do_upload_sku()
    {
        ini_set('memory_limit','512M');
        require_once(Kohana::find_file('vendor','PHPExcel/PHPExcel/IOFactory'));
        $active_id = $_POST['id'];
        $file      = $_FILES['file']['tmp_name'];
        if($file){
            $objPHPExcel  = PHPExcel_IOFactory::load($file);
            $objWorkSheet = $objPHPExcel->getActiveSheet();
            $maxRow       = $objPHPExcel->getActiveSheet()
                ->getHighestRow();
            //行 4
            $maxColumn = PHPExcel_Cell::columnIndexFromString($objPHPExcel->getActiveSheet()
                ->getHighestColumn());
            //列 1
            $data = array();
            for($row = 1;$row<=$maxRow;$row++){
                for($col = 0;$col<$maxColumn;$col++){
                    $val              = $objWorkSheet->getCellByColumnAndRow($col,$row)
                        ->getValue();
                    $data[$row][$col] = strtoupper(trim($val));
                }
            }
            $flag = 0;
            $sql  = DB::insert('active_skus',array(
                'sku',
                'active_id',
                'add_time'
            ));
            foreach($data as $v){
                $res = DB::select()
                    ->from('active_skus')
                    ->where('active_id','=',$active_id)
                    ->where('sku','=',$v[0])
                    ->execute('newerp')
                    ->current();
                if($res){
                    continue;
                }

                $sql = $sql->values(array(
                    $v[0],
                    $active_id,
                    date('Y-m-d H:i:s')
                ));
                $flag++;
            }
            if($flag){
                $sql->execute('newerp');
                Message::success('上传成功');
            }else{
                Message::error('上传失败');
            }
            $this->request->redirect("checkstock/upload_sku?id=".$active_id);
        }
    }


    public function action_show()
    {
        if(isset($_GET['erp_id'])){
            $erp_id = trim($_GET['erp_id']);
            if(substr($erp_id,0,1)=='e'||substr($erp_id,0,1)=='E'){
                $erp_id = substr($erp_id,1);

                $skus = DB::select('orders_sku')
                    ->from('erp_orders_products')
                    ->where('erp_orders_id','=',$erp_id)
                    ->execute('readonlyerp')
                    ->as_array(NULL,'orders_sku');
            }else{
                $sql_erporders = "SELECT
                            erp_orders_id
                FROM
                            erp_orders
                WHERE
                            orders_mail_code='".$erp_id."'
                OR
                            orders_shipping_code = '".$erp_id."'
                LIMIT 1";
                $erporders     = DB::query(Database::SELECT,$sql_erporders)
                    ->execute('readonlyerp')->get('erp_orders_id',0);

                if($erporders){
                    $skus = DB::select('orders_sku')
                        ->from('erp_orders_products')
                        ->where('erp_orders_id','=',$erporders)
                        ->execute('readonlyerp')
                        ->as_array(NULL,'orders_sku');
                }else{
                    $sql_erporders1 = "SELECT
                            erp_orders_id
                    FROM
                                erp_orders1
                    WHERE
                                orders_mail_code='".$erp_id."'
                    OR
                                orders_shipping_code = '".$erp_id."'
                    LIMIT 1";
                    $erporders1     = DB::query(Database::SELECT,$sql_erporders1)
                        ->execute('readonlyerp')
                        ->get('erp_orders_id',0);

                    $skus = DB::select('orders_sku')
                        ->from('erp_orders_products1')
                        ->where('erp_orders_id','=',$erporders1)
                        ->execute('readonlyerp')
                        ->as_array(NULL,'orders_sku');
                }
            }
            $get['status'] = 1;

            if(empty($skus)){
                $json = array(
                    'status' => 'N',
                    'info'   => '该订单号或追踪码有误，未查到对应的sku'
                );
                echo json_encode($json);
                exit;
            }else{
                $skus = DB::select(DB::expr('upper(i.sku) as sku'))
                    ->from(array(
                        'inventories',
                        'i'
                    ))
                    ->join(array(
                        'ldt_skus',
                        'l'
                    ))
                    ->on('i.sku','=','l.sku')
                    ->where('l.status','=',1)
                    //->where('l.onsale','=',1)
                    ->where('i.sku','in',$skus)
                    ->where('i.inventory_date','=',date('Y-m-01',time()))
                    ->execute('newerp')
                    ->as_array(NULL,'sku');
                //获取出入库数据
                $end   = date('Y-m-d',strtotime("+1 day",strtotime(date("Y-m-d",time()))));
                $begin = date('Y-m-01 00:00:00',strtotime(date("Y-m-d",time())));
                empty($skus)?$skus = array():$skus = checkstock::install_lists($begin,$end,$skus,true);
                if($skus){
                    $view = View::factory('sku')
                        ->set('lists',$skus)
                        ->set('get',$get)
                        ->set('status',checkstock::getStatusdicsValue('ldt_skus','status',NULL,0))
                        ->render();
                    $json = array(
                        'status' => 'Y',
                        'view'   => $view
                    );
                }else{
                    $json = array(
                        'status' => 'N',
                        'info'   => '未查询到结果'
                    );
                }
                echo json_encode($json);
                exit;
            }
        }
    }

    public function checkxls($data)
    {
        $str = '';
        $num = count($data);
        for($i=2;$i<=$num;$i++){
            $_str = '';
            $sku=trim($data[$i]['A']);
            $pd_time=trim($data[$i]['F']);
            $quantity=trim($data[$i]['G']);
            if($i>2){
                if($sku==trim($data[$i-1]['A'])){
                    $_str .=  '你输入的第'.($i-1).'行和第'.$i."行重复，只提交了该SKU的第一个;<br/>";
                }
            }
            if(!isset($sku)||empty($sku))
                $_str .= '你输入的第'.$i."行SKU不能为空<br/>";

            if(!isset($pd_time)||empty($pd_time))
                $_str .= '你输入的第'.$i."行盘点时间<br/>";

            if(!$quantity)
                $_str .= '你输入的第'.$i."行盘点量不能为空<br/>";

            if(!preg_match('/^([1-2][0-9]{3})[\/|\-](0?[1-9]|10|11|12)[\/|\-]([1-2]?[0-9]|0[1-9]|30|31)$/',$pd_time))
                $_str .= '你输入的第'.$i."行日期不符合规则<br/>";

            if(!preg_match('/^(\d+)?$/',$quantity))
                $_str .= '你输入的第'.$i."行数量不符合规则<br/>";

            if(!Deliverycheck::check_sku($sku))
                $_str .= '你输入的第'.$i."行SKU有误<br/>";

            if($_str != ''){unset($data[$i]);}
            $str .= $_str;
        }
        $data['error'] = $str;
        return $data;
    }
}